#include "main_interface.h"
#include "ui_main_interface.h"




#include "xlsxdocument.h"
#include "xlsxchartsheet.h"
#include "xlsxcellrange.h"
#include "xlsxchart.h"
#include "xlsxrichstring.h"
#include "xlsxworkbook.h"

#include <QToolTip>
#include <QDebug>
#include <QMessageBox>
#include <QTimer>
#include <QSqlError>
#include <QFileDialog>
#include <QSqlRecord>
#include <QCryptographicHash>

#include <QPair>


MainInterface::MainInterface(QWidget *parent)
    : QMainWindow(parent)
    , ui(new Ui::MainInterface)
{
    ui->setupUi(this);

    p0_initializeInformationManagePage();

}

MainInterface::~MainInterface()
{
    //释放该连接时，需要先获取该连接的连接名，然后先关闭当前数据库，并将当前数据库使用默认构造函数覆盖，最后再移除当前连接。
    QSqlDatabase db = QSqlDatabase::database("mysql_connection1");
    db.close();//关闭数据库
    db = QSqlDatabase();
    QSqlDatabase::removeDatabase("mysql_connection1");//断开与数据库的连接

    delete ui;
}

void MainInterface::p0_initializeInformationManagePage()
{
    //数据导出
    connect(ui->exportRecordPushButton, &QPushButton::clicked, this, [&](){
        if(p0_exportData2XLSX()){
            //QMessageBox::information(NULL, "提示","操作完毕",QMessageBox::Ok , QMessageBox::Ok);
            QMessageBox* infoBox = new QMessageBox(QMessageBox::Information, tr("信息提示"), tr("表格数据导出成功！"));
            QTimer::singleShot(2000, infoBox,&QMessageBox::deleteLater);
            infoBox->show();
        }else{
            //QMessageBox::information(NULL, "提示","操作完毕",QMessageBox::Ok , QMessageBox::Ok);
            QMessageBox* infoBox = new QMessageBox(QMessageBox::Critical, tr("信息提示"), tr("表格数据导出失败！"));
            QTimer::singleShot(2000, infoBox,&QMessageBox::deleteLater);
            infoBox->show();
        }
    });

    //添加记录
    connect(ui->addRecordPushButton, &QPushButton::clicked, this, [&](){
        QString tableName = p0_m_use2TableNameHash[ui->tableSelectComboBox->currentText()];
        if(tableName == "student_information"){
            emit p0_show2InsertStudentInformationDialog();
        }else if(tableName == "teacher_information"){
            emit p0_show2InsertTeacherInformationDialog();
        }
    });

    //单条记录修改
    connect(ui->singleAlterRecordPushButton, &QPushButton::clicked,
            this, &MainInterface::p0_singleAlterRecord);

    //数据导入
    connect(ui->importRecordPushButton, &QPushButton::clicked, this, [&](){
        QStringList fileNameList = QFileDialog::getOpenFileNames(this, QString("打开文件"), "", tr("表格/excel格式(*.xls;*.xlsx)"));
        qDebug()<<"fileNameList:"<<fileNameList;
        if(fileNameList.size() == 0){
            return;
        }

        if(p0_importData2DataBase(fileNameList) == false){//导入数据成功
            QMessageBox* infoBox = new QMessageBox(QMessageBox::Critical, tr("错误提示"), tr("导入的数据格式有误，请重新输入！"));
            QTimer::singleShot(2000, infoBox,&QMessageBox::deleteLater);
            infoBox->show();
        }else{
            QMessageBox* infoBox = new QMessageBox(QMessageBox::Information, tr("信息提示"), tr("导入数据成功。"));
            QTimer::singleShot(2000, infoBox,&QMessageBox::deleteLater);
            infoBox->show();

            p0_searchTableData();
        }
    });

    //搜索按钮
    connect(ui->searchPushButton, &QPushButton::clicked, this, &MainInterface::p0_searchTableData);

    //改变选表产生的事件
    connect(ui->tableSelectComboBox, static_cast<void (QComboBox::*)(const QString&)>(&QComboBox::currentTextChanged),
            this, [&](QString selectItemText){
                //修改的主键清空
                p0_m_primaryKeyValuePreviousHash.clear();

                //修改位置清空
                p0_m_alterRows.clear();
                p0_m_alterPosition.clear();
                //现在的页码
                p0_m_nowPage = 1;

                //下拉选项框的字段显示
                p0_tableFieldComboxShow(p0_m_use2TableNameHash[selectItemText]);
                //表的数据
                p0_allTableInformationShow();

                if(p0_m_use2TableNameHash[selectItemText] == "permission_manage_information"){
                    ui->addRecordPushButton->setEnabled(false);
                    ui->singleAlterRecordPushButton->setEnabled(false);
                    ui->batchDeletePushButton->setEnabled(false);
                    ui->importRecordPushButton->setEnabled(false);
                }else{
                    ui->addRecordPushButton->setEnabled(true);
                    ui->singleAlterRecordPushButton->setEnabled(true);
                    ui->batchDeletePushButton->setEnabled(true);
                    ui->importRecordPushButton->setEnabled(true);
                }
            });

    //批量修改按钮
    connect(ui->batchAlterPushButton, &QPushButton::clicked, this, &MainInterface::p0_batchAlterRecord);

    //批量删除
    connect(ui->batchDeletePushButton, &QPushButton::clicked, this, &MainInterface::p0_batchDeleteRecord);

    //跳转页
    connect(ui->jumpPagePushButton, &QAbstractButton::clicked,
            this, [&](){
                if(p0_m_recordAmount > 0){
                    int nowPage = ui->pageLineEdit->text().toInt();
                    qDebug()<<"nowPage:"<<nowPage;

                    if(nowPage > p0_m_pageAmount){//输入的页数大于最大页
                        p0_m_nowPage = 1;
                        ui->pageLineEdit->setText(QString::number(p0_m_nowPage));
                    }else{
                        p0_m_nowPage = nowPage;
                    }

                    if(p0_m_lineEditText.size() == 0){
                        p0_allTableInformationShow();
                    }else{
                        p0_partTableInformationShow();
                    }
                }
            });

    //第一页
    connect(ui->firstPagePushButton, &QAbstractButton::clicked,
            this, [&](){
                if(p0_m_recordAmount > 0){
                    p0_m_nowPage = 1;
                    ui->pageLineEdit->setText(QString::number(p0_m_nowPage));
                    if(p0_m_lineEditText.size() == 0){
                        p0_allTableInformationShow();
                    }else{
                        p0_partTableInformationShow();
                    }
                }
            });

    //最后一页
    connect(ui->lastPagePushButton, &QAbstractButton::clicked,
            this, [&](){
                if(p0_m_recordAmount > 0){
                    p0_m_nowPage = p0_m_pageAmount;
                    ui->pageLineEdit->setText(QString::number(p0_m_nowPage));
                    if(p0_m_lineEditText.size() == 0){
                        p0_allTableInformationShow();
                    }else{
                        p0_partTableInformationShow();
                    }
                }
            });

    //上一页
    connect(ui->previousPagePushButton, &QAbstractButton::clicked,
            this, [&](){
                if(p0_m_recordAmount > 0){
                    p0_m_nowPage = ((p0_m_nowPage == 1)?1:(p0_m_nowPage-1));
                    ui->pageLineEdit->setText(QString::number(p0_m_nowPage));
                    if(p0_m_lineEditText.size() == 0){
                        p0_allTableInformationShow();
                    }else{
                        p0_partTableInformationShow();
                    }
                }
            });

    //下一页
    connect(ui->nextPagePushButton, &QAbstractButton::clicked,
            this, [&](){
                if(p0_m_recordAmount > 0){
                    p0_m_nowPage = ((p0_m_nowPage == p0_m_pageAmount)?p0_m_pageAmount:(p0_m_nowPage+1));
                    ui->pageLineEdit->setText(QString::number(p0_m_nowPage));
                    if(p0_m_lineEditText.size() == 0){
                        p0_allTableInformationShow();
                    }else{
                        p0_partTableInformationShow();
                    }
                }
            });

    ui->searchObscureCheckBox->setChecked(true);

    p0_initializeInformationManagePageSetStyleSheet();

    p0_initializeInformationManagePageTableHash();

    p0_initializeInformationManagePageTableViewModel();

    p0_initializeInformationManagePageContent();
}

void MainInterface::p0_initializeInformationManagePageSetStyleSheet()
{
    //第一页
    ui->firstPagePushButton->setStyleSheet("QPushButton{border-image: url(:/Image/firstPage.png)}");
    //前一页
    ui->previousPagePushButton->setStyleSheet("QPushButton{border-image: url(:/Image/previousPage.png)}");
        //下一页
    ui->nextPagePushButton->setStyleSheet("QPushButton{border-image:url(:/Image/nextPage.png)}");
    //最后一页
    ui->lastPagePushButton->setStyleSheet("QPushButton{border-image:url(:/Image/lastPage.png)}");
    //跳转页
    ui->jumpPagePushButton->setStyleSheet("QPushButton{border-image:url(:/Image/jumpPage.png)}");
    //页数输入
    ui->pageLineEdit->setStyleSheet("background-color:white;border-width:0;border-style:outset;color:black;solid white");
    //批量保存
    // ui->batchAlterPushButton->setIcon(QIcon(":/Image/batchSave.png"));
    // ui->batchAlterPushButton->setIconSize(QSize(ui->batchAlterPushButton->width(), ui->batchAlterPushButton->height()));
    // ui->batchAlterPushButton->setStyleSheet("QPushButton{background:rgba(0,0,0,0);border:1px solid rgba(0,0,0,0);}");


    //输入框样式
    ui->searchLineEdit->setPlaceholderText(QStringLiteral("请输入要查询的内容"));
}

void MainInterface::p0_initializeInformationManagePageTableHash()
{
    p0_m_tableName2UseHash["student_information"] = "学生信息表";

    QPair<QString, QString> pair;
    p0_m_tableFieldName2UseHash[QPair("student_information", "student_id")] = "编号";
    p0_m_tableFieldName2UseHash[QPair("student_information","name")] = "姓名";
    p0_m_tableFieldName2UseHash[QPair("student_information","sex")] = "性别";
    p0_m_tableFieldName2UseHash[QPair("student_information","categorization")] = "工种";
    p0_m_tableFieldName2UseHash[QPair("student_information","section")] = "部门";
    p0_m_tableFieldName2UseHash[QPair("student_information","class")] = "班级";
    p0_m_tableFieldName2UseHash[QPair("student_information","job_category")] = "分类";
    p0_m_tableFieldName2UseHash[QPair("student_information","title")] = "职称";
    p0_m_tableFieldName2UseHash[QPair("student_information","work_unit")] = "单位";
    p0_m_tableFieldName2UseHash[QPair("student_information","remark")] = "备注";

    p0_m_use2TableNameHash["学生信息表"] = "student_information";

    p0_m_use2TableFieldNameHash[QPair("学生信息表", "编号")] = "student_id";
    p0_m_use2TableFieldNameHash[QPair("学生信息表","姓名")] = "name";
    p0_m_use2TableFieldNameHash[QPair("学生信息表","性别")] = "sex";
    p0_m_use2TableFieldNameHash[QPair("学生信息表","工种")] = "categorization";
    p0_m_use2TableFieldNameHash[QPair("学生信息表","部门")] = "section";
    p0_m_use2TableFieldNameHash[QPair("学生信息表","班级")] = "class";
    p0_m_use2TableFieldNameHash[QPair("学生信息表","分类")] = "job_category";
    p0_m_use2TableFieldNameHash[QPair("学生信息表","职称")] = "title";
    p0_m_use2TableFieldNameHash[QPair("学生信息表","单位")] = "work_unit";
    p0_m_use2TableFieldNameHash[QPair("学生信息表","备注")] = "remark";

    p0_m_tableName2UseHash["teacher_information"] = "教师信息表";

    p0_m_tableFieldName2UseHash[QPair("teacher_information", "teacher_id")] = "编号";
    p0_m_tableFieldName2UseHash[QPair("teacher_information","name")] = "姓名";
    p0_m_tableFieldName2UseHash[QPair("teacher_information","role_id")] = "角色";

    p0_m_use2TableNameHash["教师信息表"] = "teacher_information";
    p0_m_use2TableFieldNameHash[QPair("教师信息表", "编号")] = "teacher_id";
    p0_m_use2TableFieldNameHash[QPair("教师信息表","姓名")] = "name";
    p0_m_use2TableFieldNameHash[QPair("教师信息表","角色")] = "role_id";


    p0_m_tableName2UseHash["permission_manage_information"] = "角色信息表";

    p0_m_tableFieldName2UseHash[QPair("permission_manage_information", "user_id")] = "用户编号";
    p0_m_tableFieldName2UseHash[QPair("permission_manage_information", "role_id")] = "角色";

     p0_m_use2TableNameHash["角色信息表"] = "permission_manage_information";

    p0_m_use2TableFieldNameHash[QPair("角色信息表", "用户编号")] = "user_id";
    p0_m_use2TableFieldNameHash[QPair("角色信息表", "角色")] = "role_id";

}

void MainInterface::p0_initializeInformationManagePageTableViewModel()
{
    //显示鼠标显示tableView上的文字信息
    //开启界面鼠标捕获功能
    ui->recordsTableView->viewport()->setMouseTracking(true);

    //显示鼠标显示tableView上的文字信息
    //开启界面鼠标捕获功能
    ui->recordsTableView->viewport()->setMouseTracking(true);
    connect(ui->recordsTableView, &QTableView::entered,
            this, [](const QModelIndex &index)
            {
                if(index.isValid() == false) return;
                else{
                    // qDebug()<<"index.data().toString():"<<index.data().toString();
                    QToolTip::showText(QCursor::pos(), index.data().toString());
                }
            }
            );

    //1 数据模型
    p0_m_tableDataModel = new QStandardItemModel(this);
    //2 选择模型
    p0_m_selectionModel = new QItemSelectionModel(p0_m_tableDataModel);
    //3 设置模型
    ui->recordsTableView->setModel(p0_m_tableDataModel);
    ui->recordsTableView->setSelectionModel(p0_m_selectionModel);

    //选择行为
    ui->recordsTableView->setSelectionBehavior (QAbstractItemView::SelectRows);
    //ui->recordsTableView->setSelectionMode ( QAbstractItemView::SingleSelection);
    ui->recordsTableView->setAlternatingRowColors(true);

    //隐藏左边的列名
    ui->recordsTableView->verticalHeader()->hide();

    //根据内容自适应宽度
    ui->recordsTableView->horizontalHeader()->setSectionResizeMode(QHeaderView::ResizeToContents);
    ui->recordsTableView->verticalHeader()->setSectionResizeMode(QHeaderView::Fixed);

    //设置最大和最小列宽
    ui->recordsTableView->horizontalHeader()->setMinimumSectionSize(104);
    ui->recordsTableView->horizontalHeader()->setMaximumSectionSize(200);

    connect(ui->recordsTableView->itemDelegate(),&QAbstractItemDelegate::closeEditor,this,[&](){
        qDebug()<<"&QAbstractItemDelegate::closeEditor";
        //记录被修改的行,方便批量修改
        p0_m_alterRows.insert(p0_m_selectionModel->currentIndex().row());
        //记录被修改的位置
        p0_m_alterPosition[p0_m_selectionModel->currentIndex().row()].append(p0_m_selectionModel->currentIndex().column());
    });

   // connect(ui->recordsTableView, SIGNAL(doubleClicked(const QModelIndex &)), this, SLOT(ClickCell(const QModelIndex &)));

    //记录编辑之前的主键值
    connect(ui->recordsTableView, &QTableView::doubleClicked, this, [&](const QModelIndex& modelIndex){
        QModelIndex tmpIndex =  p0_m_tableDataModel->index(modelIndex.row(), p0_m_primaryKeyColumnIndex);
        QVariant keyValue = p0_m_tableDataModel->data(tmpIndex);

        qDebug()<<"编辑过行："<<modelIndex.row();
        qDebug()<<"编辑过行的主键"<<p0_m_tableDataModel->data(tmpIndex);

        //主键没有在其中，就添加
        if(p0_m_primaryKeyValuePreviousHash.find(modelIndex.row()) != p0_m_primaryKeyValuePreviousHash.end()){

        }else{
            qDebug()<<"添加编辑行的主键数据";
            p0_m_primaryKeyValuePreviousHash[modelIndex.row()] = p0_m_tableDataModel->data(tmpIndex);
        }

        //打印映射表
        // QHash<int, QVariant>::const_iterator iter = p0_m_primaryKeyValuePreviousHash.constBegin();
        // while(iter != p0_m_primaryKeyValuePreviousHash.constEnd()){
        //     qDebug()<<"iter.key()"<<iter.key();
        //     qDebug()<<"iter.value()"<<iter.value();
        //     iter++;
        // }

    });

    // connect(p0_m_tableDataModel, &QStandardItemModel::itemChanged, this, [&](QStandardItem *item){
    //     qDebug()<<item->data();
    // });

    //阻塞信号
    ui->recordsTableView->itemDelegate()->blockSignals(true);
}

void MainInterface::p0_initializeInformationManagePageContent()
{

    //添加表信息
    QSqlQuery query(QSqlDatabase::database("mysql_connection1"));

    QString tmpSql = QString("SHOW TABLES");
    query.exec(tmpSql);
    int index = 0;
    while(query.next()){
        QString tableName = query.value(0).toString();

        //如果表没有在映射表中，就不显示
        if(p0_m_tableName2UseHash.find(tableName) != p0_m_tableName2UseHash.end()){
            ui->tableSelectComboBox->addItem(p0_m_tableName2UseHash[tableName]);

            if(index == 0){
                ui->tableSelectComboBox->setCurrentText(p0_m_tableName2UseHash[tableName]);
                p0_tableFieldComboxShow(tableName);
            }
            index++;
        }
    }

    //默认选择学生信息
    ui->tableSelectComboBox->setCurrentText(p0_m_tableName2UseHash["student_information"]);
}

void MainInterface::p0_tableInformationTableViewDataShowBySqlQuery(QSqlQuery &query)
{
    // //设置行和列
    int row = 0;
    while(query.next()){
        row++;
    }
    query.seek(-1);

    p0_m_tableDataModel->setRowCount(row);

    row = 0;
    //设置表中值
    while(query.next()){//ui->Page1_tableView->model()->headerData(i,Qt::Horizontal).toString();
        for(int colNum = 0;colNum < p0_m_tableFieldQueue.size();colNum++){
            if(p0_m_use2TableFieldNameHash[QPair(ui->tableSelectComboBox->currentText(),ui->recordsTableView->model()->headerData(colNum, Qt::Horizontal).toString())] ==  "role_id" && (p0_m_use2TableNameHash[ui->tableSelectComboBox->currentText()] == "permission_manage_information")){
                QComboBox *tableFieldComboBox = new QComboBox(this);

                //tableFieldComboBox->setStyleSheet("QComboBox{color:rgb(0, 0, 0);}");
                foreach (QString tmpData, p0_m_permissionTableFieldSelect2ComboxHash) {
                    //qDebug()<<"下拉框信息:"<<tmpData;
                    tableFieldComboBox->addItem(tmpData);
                }
                //设置所选内容
                tableFieldComboBox->setCurrentText(p0_m_permissionTableFieldSelect2ComboxHash[query.value(colNum).toString()]);

                ui->recordsTableView->setIndexWidget(p0_m_tableDataModel->index(row, colNum),  tableFieldComboBox);

                connect(tableFieldComboBox, &QComboBox::currentTextChanged,
                        this, [&](){
                        // 获取点击的按钮的位置
                        QComboBox * comboxPosition = dynamic_cast<QComboBox*>(this->sender());
                        int x = comboxPosition->frameGeometry().x();
                        int y = comboxPosition->frameGeometry().y();
                        QModelIndex modelIndex = ui->recordsTableView->indexAt(QPoint(x,y));

                        //记录被修改的行,方便批量修改
                        p0_m_alterRows.insert(modelIndex.row());
                        //记录被修改的位置
                        p0_m_alterPosition[modelIndex.row()].append(modelIndex.column());

                        QModelIndex tmpIndex =  p0_m_tableDataModel->index(modelIndex.row(), p0_m_primaryKeyColumnIndex);
                        QVariant keyValue = p0_m_tableDataModel->data(tmpIndex);

                        qDebug()<<"编辑过行："<<modelIndex.row();
                        qDebug()<<"编辑过行的主键"<<p0_m_tableDataModel->data(tmpIndex);

                        //主键没有在其中，就添加
                        if(p0_m_primaryKeyValuePreviousHash.find(modelIndex.row()) != p0_m_primaryKeyValuePreviousHash.end()){
                            qDebug()<<"添加编辑行的主键数据已存在"<<modelIndex.row();
                        }else{
                            qDebug()<<"添加编辑行:"<<modelIndex.row();
                            qDebug()<<"主键数据"<<keyValue;
                            p0_m_primaryKeyValuePreviousHash[modelIndex.row()] = keyValue;
                        }
                });
                //Qt::UniqueConnections 不适用于 lambda、非成员函数和函子；它们仅适用于连接成员函数。

            }else if(p0_m_use2TableFieldNameHash[QPair(ui->tableSelectComboBox->currentText(),ui->recordsTableView->model()->headerData(colNum, Qt::Horizontal).toString())] ==  "role_id" && (p0_m_use2TableNameHash[ui->tableSelectComboBox->currentText()] == "teacher_information")){
                qDebug()<<"更换教师字段属性:"<<p0_m_permissionTableFieldSelect2ComboxHash[query.value(colNum).toString()];
                QModelIndex index =  p0_m_tableDataModel->index(row, colNum);
                p0_m_tableDataModel->setData(index, p0_m_permissionTableFieldSelect2ComboxHash[query.value(colNum).toString()], Qt::DisplayRole);
            }else{
                QModelIndex index =  p0_m_tableDataModel->index(row, colNum);
                p0_m_tableDataModel->setData(index, query.value(colNum), Qt::DisplayRole);
            }
            // p0_m_tableDataModel->setItem(row, colNum, new QStandardItem(query.value(colNum).toString()));
        }
        row++;
    }


    ui->recordsTableView->setModel(p0_m_tableDataModel);
    ui->recordsTableView->show();

    //默认选中第一行
    // QItemSelection selection(p0_m_tableDataModel->index(0, 0), p0_m_tableDataModel->index(0, p0_m_tableDataModel->columnCount() - 1));
    // p0_m_selectionModel->select(selection,  QItemSelectionModel::Select);

    //记录被修改过的行
    //修改后的数据清空
    p0_m_alterRows.clear();
    p0_m_alterPosition.clear();
    p0_m_primaryKeyValuePreviousHash.clear();


    ui->recordsTableView->itemDelegate()->blockSignals(false);
}

void MainInterface::p0_allTableInformationShow()
{
    QSqlQuery query(QSqlDatabase::database("mysql_connection1"));

    //表头栏显示
    p0_tableViewHeadingShow();

    QString tmpSql = QString("SELECT ");
    int row = 0;
    //拼接查询字段名
    foreach (QString tableField, p0_m_tableFieldQueue){
        if(row > 0){
            tmpSql += ",";
        }
        row++;
        tmpSql += tableField;
    }
    tmpSql += QString(" FROM ");
    tmpSql += p0_m_use2TableNameHash[ui->tableSelectComboBox->currentText()];
    //qDebug()<<"tmpSql:"<<tmpSql;
    query.exec(tmpSql);
    //统计总页数
    p0_m_recordAmount = 0;
    while(query.next()) p0_m_recordAmount++;
    p0_m_pageAmount = (p0_m_recordAmount%p0_m_pageUnit == 0)?(p0_m_recordAmount/p0_m_pageUnit):(p0_m_recordAmount/p0_m_pageUnit+1);

    ui->totalPageLabel->setText("共" + QString::number(p0_m_pageAmount) + "页");
    ui->pageInformationLabel->setText("显示" + QString::number((p0_m_nowPage - 1)*p0_m_pageUnit + 1) + "到" + QString::number((p0_m_nowPage*p0_m_pageUnit>p0_m_recordAmount)?p0_m_recordAmount:(p0_m_nowPage*p0_m_pageUnit))+"，共"+ QString::number(p0_m_recordAmount) + "条记录");
    ui->pageLineEdit->setText(QString::number(p0_m_nowPage));


    tmpSql += QString(" LIMIT %1 OFFSET %2").arg(p0_m_pageUnit).arg((p0_m_nowPage - 1)*p0_m_pageUnit);
    // tmpSql = QString("SELECT student_id,name,sex,section,job_category,categorization,class,title,work_unit,remark FROM student_information LIMIT %1 OFFSET %2").arg(p0_m_pageUnit).arg((p0_m_nowPage - 1)*p0_m_pageUnit);
    query.exec(tmpSql);

    p0_tableInformationTableViewDataShowBySqlQuery(query);

}

void MainInterface::p0_partTableInformationShow(){
    p0_m_lineEditText = ui->searchLineEdit->text();

    QSqlQuery query(QSqlDatabase::database("mysql_connection1"));

    //表头栏显示，并返回字段名
    p0_tableViewHeadingShow();

    QString tmpSql = QString("SELECT ");
    int row = 0;
    //拼接查询字段名
    foreach (QString tableField, p0_m_tableFieldQueue){
        if(row > 0){
            tmpSql += ",";
        }
        row++;
        tmpSql += tableField;
    }
    tmpSql += QString(" FROM ");
    tmpSql += p0_m_use2TableNameHash[ui->tableSelectComboBox->currentText()];
    tmpSql += QString(" WHERE ") + p0_m_use2TableFieldNameHash[QPair(ui->tableSelectComboBox->currentText(), ui->tableFieldSelectComboBox->currentText())];

    //是否模糊查询
    if(ui->searchObscureCheckBox->isChecked() == true){
        if(ui->tableFieldSelectComboBox->currentText() ==  "角色" && ui->tableSelectComboBox->currentText() == "角色信息表"){
            tmpSql += QString(" LIKE  \'%%1%\'").arg(p0_m_combox2PermissionTableFieldSelectHash[p0_m_lineEditText]);
        }else{
            tmpSql += QString(" LIKE  \'%%1%\'").arg(p0_m_lineEditText);
        }
    }else{
        if(ui->tableFieldSelectComboBox->currentText() ==  "角色" && ui->tableSelectComboBox->currentText() == "角色信息表"){
            tmpSql += QString(" = \'%1\'").arg(p0_m_combox2PermissionTableFieldSelectHash[p0_m_lineEditText]);
        }else{
            tmpSql += QString(" = \'%1\'").arg(p0_m_lineEditText);
        }
    }


    //qDebug()<<"tmpSql:"<<tmpSql;
    query.exec(tmpSql);

    //统计总页数
    p0_m_recordAmount = 0;
    while(query.next()) p0_m_recordAmount++;
    p0_m_pageAmount = (p0_m_recordAmount%p0_m_pageUnit == 0)?(p0_m_recordAmount/p0_m_pageUnit):(p0_m_recordAmount/p0_m_pageUnit+1);

    ui->totalPageLabel->setText("共" + QString::number(p0_m_pageAmount) + "页");
    ui->pageInformationLabel->setText("显示" + QString::number((p0_m_nowPage - 1)*p0_m_pageUnit + 1) + "到" + QString::number((p0_m_nowPage*p0_m_pageUnit>p0_m_recordAmount)?p0_m_recordAmount:(p0_m_nowPage*p0_m_pageUnit))+"，共"+ QString::number(p0_m_recordAmount) + "条记录");
    ui->pageLineEdit->setText(QString::number(p0_m_nowPage));

    tmpSql += QString(" LIMIT %1 OFFSET %2").arg(p0_m_pageUnit).arg((p0_m_nowPage - 1)*p0_m_pageUnit);
    // tmpSql = QString("SELECT student_id,name,sex,section,job_category,categorization,class,title,work_unit,remark FROM student_information LIMIT %1 OFFSET %2").arg(p0_m_pageUnit).arg((p0_m_nowPage - 1)*p0_m_pageUnit);
    query.exec(tmpSql);
    p0_tableInformationTableViewDataShowBySqlQuery(query);

}

void MainInterface::p0_tableViewHeadingShow()
{
    p0_m_tableDataModel->clear();
    //添加表字段信息
    p0_m_tableFieldQueue.clear();//保存表的字段名（控制select中字段值顺序）

    //移除之前的数据
    p0_m_tableDataModel->removeRows(0, p0_m_tableDataModel->rowCount());
    //水平表头可见
    ui->recordsTableView->horizontalHeader()->setVisible(true);

    QSqlQuery query(QSqlDatabase::database("mysql_connection1"));

    QString  tmpSql = QString("DESCRIBE ") + p0_m_use2TableNameHash[ui->tableSelectComboBox->currentText()];
    query.exec(tmpSql);
    int row = 0;
    while(query.next()){
        //p0_m_tableDataModel->setHorizontalHeaderItem(row, new QStandardItem(p0_m_tableFieldName2UseHash[QPair(p0_m_use2TableNameHash[ui->tableSelectComboBox->currentText()], query.value(0).toString())]));
        // qDebug()<<"p0_m_use2TableNameHash[ui->tableSelectComboBox->currentText()]:"<<p0_m_use2TableNameHash[ui->tableSelectComboBox->currentText()];
        // qDebug()<<"query.value(0).toString():"<<query.value(0).toString();
        // p0_m_tableDataModel->setHorizontalHeaderItem(0, new QStandardItem("编号"));
        //setItem没有则会新增，setData仅修改已存在的项
        //设置表头，如不设置则使用数据库中的默认表头
        QString filedName = query.value(0).toString();
        p0_m_tableFieldQueue.enqueue(filedName);
        p0_m_tableDataModel->setHorizontalHeaderItem(row, new QStandardItem(p0_m_tableFieldName2UseHash[QPair(p0_m_use2TableNameHash[ui->tableSelectComboBox->currentText()], filedName)]));
        row++;
    }

    //让教师信息表的角色id无法编辑
    if(p0_m_use2TableNameHash[ui->tableSelectComboBox->currentText()] == "teacher_information"){
        for(int col = 0;col < p0_m_tableFieldQueue.size();col++){
            if(p0_m_tableFieldQueue[col] == "role_id"){
                ui->recordsTableView->setItemDelegateForColumn(col, &p0_m_readOnlyDelegate);
                break;
            }
        }
    }else{//恢复其他表的设计
        int cols = p0_m_tableDataModel->columnCount();
        QAbstractItemDelegate* defaultdelegate = ui->recordsTableView->itemDelegateForColumn(0);
        for(int loop = 1; loop < cols ;loop ++)
        {
            QAbstractItemDelegate* nowdelegate = ui->recordsTableView->itemDelegateForColumn(loop);
            ReadOnlyDelegate * showdelegate = static_cast<ReadOnlyDelegate *>(nowdelegate);
            if(showdelegate)            //表示当前的代理是自定义的代理
            {
                ui->recordsTableView->setItemDelegateForColumn(loop, defaultdelegate);
            }
        }
    }


}

void MainInterface::p0_batchAlterRecord()
{
    if(p0_m_alterRows.size() == 0){
        QMessageBox* infoBox = new QMessageBox(QMessageBox::Critical, tr("信息提示"), tr("没有修改过的数据！"));
        QTimer::singleShot(2000, infoBox,&QMessageBox::deleteLater);
        infoBox->show();
        return;
    }

    auto button = QMessageBox::question(this,  tr("信息提示"),
                                        QString("是否确认对修改过的%1行数据进行修改？").arg(p0_m_alterRows.size()),  QString("确定"), QString("取消"), 0);

    if (button == 0) {//修改记录信息
        QList<bool> execResultList;
        bool execResult = false;
        QSqlDatabase db = QSqlDatabase::database("mysql_connection1");
        if(db.transaction()){
            QSqlQuery query(db);

            //UPDATE table_anem SET column_name1 = value1, column_name2 = value2... WHERE
            QString tmpSql = QString("UPDATE ");
            tmpSql += p0_m_use2TableNameHash[ui->tableSelectComboBox->currentText()];
            tmpSql += QString(" SET ");

            QHash<QString, QVariantList> tableAlterFiledValue;
            QHash<QString, QVariantList> tableAlterPrimaryKeyValue;
            int row = 0;
            foreach (QString tableField, p0_m_tableFieldQueue){
                if(row > 0){
                    tmpSql += ",";
                }
                row++;
                tmpSql += tableField + QString(" = ? ");
            }

            tmpSql += QString(" WHERE ") + p0_m_tablePrimaryKeyField.toString()  + QString(" = ? ");

            qDebug()<<tmpSql;
            query.prepare(tmpSql);


            foreach(int rowNum, p0_m_alterRows){
                qDebug()<<"rowNum:"<<rowNum;
                //QVariantList

                int colNum = 0;
                //获得每个变量改变的值
                foreach (QString tableField, p0_m_tableFieldQueue) {
                    //判断更新的主键和之前的主键有没有重复(有重复则不更新)
                    if(colNum == p0_m_primaryKeyColumnIndex){
                        qDebug()<<"p0_m_tableDataModel->data(p0_m_tableDataModel->index(rowNum, colNum)):"<<p0_m_tableDataModel->data(p0_m_tableDataModel->index(rowNum, colNum));
                        qDebug()<<"p0_m_primaryKeyValuePreviousHash[rowNum]:"<<p0_m_primaryKeyValuePreviousHash[rowNum];
                        if(p0_m_tableDataModel->data(p0_m_tableDataModel->index(rowNum, colNum)) != p0_m_primaryKeyValuePreviousHash[rowNum]){
                            qDebug()<<"主键已被修改过";
                            if(p0_judgePrimaryKeyIsExistence(p0_m_tableDataModel->data(p0_m_tableDataModel->index(rowNum, colNum)))){
                                QMessageBox* infoBox = new QMessageBox(QMessageBox::Critical, tr("信息提示"), tr("修改的数据有重复的数据，请重新修改！"));
                                QTimer::singleShot(2000, infoBox,&QMessageBox::deleteLater);
                                infoBox->show();
                                //清空修改后的数据
                                p0_searchTableData();
                                return;
                            }
                        }


                    }

                    //添加表中字段中，并对字段做映射
                    if((p0_m_use2TableNameHash[ui->tableSelectComboBox->currentText()] == "permission_manage_information") && ui->recordsTableView->model()->headerData(colNum, Qt::Horizontal).toString() ==  "角色"){
                        qDebug()<<"转换权限表中角色的字段内容";
                        //获得定义控件中的值
                        QString comboxData = static_cast<QComboBox *>(ui->recordsTableView->indexWidget(p0_m_tableDataModel->index(rowNum, colNum)))->currentText();
                        qDebug()<<comboxData;

                        tableAlterFiledValue[tableField].append(p0_m_combox2PermissionTableFieldSelectHash[comboxData]);
                    }else if(p0_m_use2TableNameHash[ui->tableSelectComboBox->currentText()] == "teacher_information" && ui->recordsTableView->model()->headerData(colNum, Qt::Horizontal).toString() ==  "角色"){
                        qDebug()<<"转换教师表中角色的字段内容";
                        tableAlterFiledValue[tableField].append(p0_m_combox2PermissionTableFieldSelectHash[p0_m_tableDataModel->data(p0_m_tableDataModel->index(rowNum, colNum)).toString()]);
                    }else{
                        tableAlterFiledValue[tableField].append(p0_m_tableDataModel->data(p0_m_tableDataModel->index(rowNum, colNum)));
                    }
                    colNum++;
                }
                qDebug()<<"p0_m_primaryKeyValuePreviousHash[row]:"<<p0_m_primaryKeyValuePreviousHash[rowNum];
                tableAlterPrimaryKeyValue[p0_m_tablePrimaryKeyField.toString()].append(p0_m_primaryKeyValuePreviousHash[rowNum]);
            }

            //绑定变量值set value
            foreach (QString tableField, p0_m_tableFieldQueue) {
                qDebug()<<"修改的字段："<<tableField;
                qDebug()<<tableAlterFiledValue[tableField];
                query.addBindValue(tableAlterFiledValue[tableField]);
            }

            query.addBindValue(tableAlterPrimaryKeyValue[p0_m_tablePrimaryKeyField.toString()]);

            execResult = query.execBatch();
            execResultList.append(execResult);
            if(!execResult){
                qDebug()<<query.lastError().databaseText();
            }

            if(p0_m_use2TableNameHash[ui->tableSelectComboBox->currentText()] == "permission_manage_information"){
                qDebug()<<"修改权限表，变动教师表";
                tmpSql = QString("UPDATE teacher_information SET role_id = ? ")
                         + QString(" WHERE teacher_id = ? ");
                qDebug()<<tmpSql;
                query.prepare(tmpSql);
                qDebug()<<tableAlterFiledValue["role_id"];
                qDebug()<<tableAlterPrimaryKeyValue[p0_m_tablePrimaryKeyField.toString()];

                query.addBindValue(tableAlterFiledValue["role_id"]);
                query.addBindValue(tableAlterPrimaryKeyValue[p0_m_tablePrimaryKeyField.toString()]);

                execResult = query.execBatch();
                execResultList.append(execResult);
                if(!execResult){
                    qDebug()<<query.lastError().databaseText();
                }
            }

            if(p0_m_use2TableNameHash[ui->tableSelectComboBox->currentText()] == "teacher_information"){
                tmpSql = QString("UPDATE permission_manage_information SET user_id = ? ")
                         + QString(" WHERE user_id = ? ");
                qDebug()<<tmpSql;
                query.prepare(tmpSql);
                qDebug()<<tableAlterFiledValue["teacher_id"];
                qDebug()<<tableAlterPrimaryKeyValue[p0_m_tablePrimaryKeyField.toString()];

                query.addBindValue(tableAlterFiledValue["teacher_id"]);
                query.addBindValue(tableAlterPrimaryKeyValue[p0_m_tablePrimaryKeyField.toString()]);

                execResult = query.execBatch();
                execResultList.append(execResult);
                if(!execResult){
                    qDebug()<<query.lastError().databaseText();
                }

                tmpSql = QString("UPDATE login_information SET account = ? ")
                         + QString(" WHERE account = ? ");
                qDebug()<<tmpSql;
                query.prepare(tmpSql);
                qDebug()<<tableAlterFiledValue["teacher_id"];
                qDebug()<<tableAlterPrimaryKeyValue[p0_m_tablePrimaryKeyField.toString()];

                query.addBindValue(tableAlterFiledValue["teacher_id"]);
                query.addBindValue(tableAlterPrimaryKeyValue[p0_m_tablePrimaryKeyField.toString()]);

                execResult = query.execBatch();
                execResultList.append(execResult);
                if(!execResult){
                    qDebug()<<query.lastError().databaseText();
                }
            }

            if(p0_m_use2TableNameHash[ui->tableSelectComboBox->currentText()] == "student_information"){

                tmpSql = QString("UPDATE login_information SET account = ? ")
                         + QString(" WHERE account = ? ");
                qDebug()<<tmpSql;
                query.prepare(tmpSql);
                qDebug()<<tableAlterFiledValue["student_id"];
                qDebug()<<tableAlterPrimaryKeyValue[p0_m_tablePrimaryKeyField.toString()];

                query.addBindValue(tableAlterFiledValue["student_id"]);
                query.addBindValue(tableAlterPrimaryKeyValue[p0_m_tablePrimaryKeyField.toString()]);

                execResult = query.execBatch();
                execResultList.append(execResult);
                if(!execResult){
                    qDebug()<<query.lastError().databaseText();
                }
            }


            foreach (bool result, execResultList) {
                if(result == false){
                    if(!db.rollback())
                        qDebug() << "db.lastError()"<<db.lastError().databaseText(); //数据回滚

                    QMessageBox* infoBox = new QMessageBox(QMessageBox::Critical, tr("信息提示"), tr("数据修改失败！"));
                    QTimer::singleShot(2000, infoBox,&QMessageBox::deleteLater);
                    infoBox->show();
                    return;
                }
            }

            if(db.commit()){
                QMessageBox* infoBox = new QMessageBox(QMessageBox::Information, tr("信息提示"), tr("数据修改成功."));
                QTimer::singleShot(2000, infoBox,&QMessageBox::deleteLater);
                infoBox->show();

                p0_searchTableData();
            }else{
                QMessageBox* infoBox = new QMessageBox(QMessageBox::Critical, tr("信息提示"), tr("数据修改失败！"));
                QTimer::singleShot(2000, infoBox,&QMessageBox::deleteLater);
                infoBox->show();
            }

        }
    }
}

void MainInterface::p0_singleAlterRecord()
{
    //先判断是否有数据已经修改，若已经修改，则需要先保存修改，再单条修改
    if(p0_m_alterRows.size() > 0){
        QMessageBox* infoBox = new QMessageBox(QMessageBox::Critical, tr("信息提示"), tr("请先保存修改后的数据，再单条修改，谢谢！"));
        QTimer::singleShot(2000, infoBox,&QMessageBox::deleteLater);
        infoBox->show();
        return;
    }

    //获取选择模型的指针
    QItemSelectionModel *selections =  ui->recordsTableView->selectionModel();
    //获取被选中的指针列表
    QModelIndexList selected = selections->selectedIndexes();

    QSet<int> rowsSet;
    foreach (QModelIndex index, selected) {
        rowsSet.insert(index.row());
    }

    if(rowsSet.size() == 0){
        QMessageBox* infoBox = new QMessageBox(QMessageBox::Critical, tr("信息提示"), tr("没有选择修改的行！"));
        QTimer::singleShot(2000, infoBox,&QMessageBox::deleteLater);
        infoBox->show();
        return;
    }

    if(rowsSet.size() > 1){
        QMessageBox* infoBox = new QMessageBox(QMessageBox::Critical, tr("信息提示"), tr("选择的行数大于1，请重新选择！"));
        QTimer::singleShot(2000, infoBox,&QMessageBox::deleteLater);
        infoBox->show();
        return;
    }

    QModelIndex tmpIndex;
    int alterRow;
    for(QSet<int>::Iterator iter = rowsSet.begin();iter != rowsSet.end();iter++){
        alterRow = *iter;
    }
    qDebug()<<"修改的行："<<alterRow;

    tmpIndex = p0_m_tableDataModel->index(alterRow, p0_m_primaryKeyColumnIndex);
    p0_m_tableDataModel->data(tmpIndex);
    qDebug()<<"p0_m_tableDataModel->data(tmpIndex):"<<p0_m_tableDataModel->data(tmpIndex).toString();

    if(p0_m_use2TableNameHash[ui->tableSelectComboBox->currentText()] == "student_information"){
        if(p0_m_primaryKeyValuePreviousHash.find(alterRow) != p0_m_primaryKeyValuePreviousHash.end()){
            emit p0_show2AlterStudentInformationDialog(p0_m_primaryKeyValuePreviousHash[alterRow].toString());
        }else{
            emit p0_show2AlterStudentInformationDialog(p0_m_tableDataModel->data(tmpIndex).toString());
        }
    }else if(p0_m_use2TableNameHash[ui->tableSelectComboBox->currentText()] == "teacher_information"){
        if(p0_m_primaryKeyValuePreviousHash.find(alterRow) != p0_m_primaryKeyValuePreviousHash.end()){
            emit p0_show2AlterTeacherInformationDialog(p0_m_primaryKeyValuePreviousHash[alterRow].toString());
        }else{
            emit p0_show2AlterTeacherInformationDialog(p0_m_tableDataModel->data(tmpIndex).toString());
        }
    }

}

void MainInterface::p0_batchDeleteRecord()
{
    //先判断是否有数据已经修改，若已经修改，则需要先保存修改，再删除
    if(p0_m_alterRows.size() > 0){
        QMessageBox* infoBox = new QMessageBox(QMessageBox::Critical, tr("信息提示"), tr("请先保存修改后的数据，再删除，谢谢！"));
        QTimer::singleShot(2000, infoBox,&QMessageBox::deleteLater);
        infoBox->show();
        return;
    }

    //获取选择模型的指针
    QItemSelectionModel *selections =  ui->recordsTableView->selectionModel();
    //获取被选中的指针列表
    QModelIndexList selected = selections->selectedIndexes();
    //初始化删除行和删除的id
    p0_m_deleteRows.clear();

    foreach (QModelIndex index, selected) {
        p0_m_deleteRows.insert(index.row());
    }

    if(p0_m_deleteRows.size() == 0){//没有被选中的行
        QMessageBox* infoBox = new QMessageBox(QMessageBox::Critical, tr("信息提示"), tr("没有选择删除的行！"));
        QTimer::singleShot(2000, infoBox,&QMessageBox::deleteLater);
        infoBox->show();
        return;
    }

    auto button = QMessageBox::question(this,  tr("信息提示"),
                                        QString("是否确认要删除这%1行数据？").arg(p0_m_deleteRows.size()),  QString("确定"), QString("取消"), 0);
    if (button == 0) {//删除记录信息
        QList<bool> execResultList;
        bool execResult = false;
        QSqlDatabase db = QSqlDatabase::database("mysql_connection1");
        if(db.transaction()){
            QSqlQuery query(db);
            //DELETE FROM teacher_information WHERE teacher_id in ('1','2','3');
            QString tmpSql = QString("DELETE FROM ");
            tmpSql += p0_m_use2TableNameHash[ui->tableSelectComboBox->currentText()];
            tmpSql += QString(" WHERE ") + p0_m_tablePrimaryKeyField.toString();
            QString deleteValueList = QString(" in(");


            int index = 0;
            foreach(int rowNum, p0_m_deleteRows){
                if(index > 0){
                    deleteValueList += ",";
                }

                QVariant data = p0_m_tableDataModel->data(p0_m_tableDataModel->index(rowNum, p0_m_primaryKeyColumnIndex));
                if(data.type() == QVariant::String){
                    deleteValueList += QString("\'")  + p0_m_tableDataModel->data(p0_m_tableDataModel->index(rowNum, p0_m_primaryKeyColumnIndex)).toString() + QString("\'");
                }else{
                    deleteValueList += p0_m_tableDataModel->data(p0_m_tableDataModel->index(rowNum, p0_m_primaryKeyColumnIndex)).toString();
                }

                index++;
            }

            deleteValueList += QString(")");
            tmpSql += deleteValueList;
            qDebug()<<tmpSql;

            execResult = query.exec(tmpSql);
            execResultList.append(execResult);
            if(!execResult){
                qDebug()<<query.lastError().text();
            }

            //删除教师关联的角色表
            if(p0_m_use2TableNameHash[ui->tableSelectComboBox->currentText()] == "teacher_information"){
                tmpSql = QString("DELETE FROM permission_manage_information WHERE user_id ");
                tmpSql += deleteValueList;

                execResult = query.exec(tmpSql);
                execResultList.append(execResult);
                if(!execResult){
                    qDebug()<<query.lastError().text();
                }
            }

            //删除登陆表中的账号信息
            if(p0_m_use2TableNameHash[ui->tableSelectComboBox->currentText()] == "teacher_information" || p0_m_use2TableNameHash[ui->tableSelectComboBox->currentText()] == "student_information"){
                tmpSql = QString("DELETE FROM login_information WHERE account ");
                tmpSql += deleteValueList;
                qDebug()<<tmpSql;

                execResult = query.exec(tmpSql);
                execResultList.append(execResult);
                if(!execResult){
                    qDebug()<<query.lastError().text();
                }
            }

            foreach (bool result, execResultList) {
                if(result == false){
                    if(!db.rollback()){
                        qDebug()<<query.lastError().databaseText();
                    }

                    QMessageBox* infoBox = new QMessageBox(QMessageBox::Critical, tr("信息提示"), tr("表格数据删除失败！"));
                    QTimer::singleShot(2000, infoBox,&QMessageBox::deleteLater);
                    infoBox->show();

                    return;
                }
            }

            if(db.commit()){
                QMessageBox* infoBox = new QMessageBox(QMessageBox::Information, tr("信息提示"), tr("表格数据删除成功。"));
                QTimer::singleShot(2000, infoBox,&QMessageBox::deleteLater);
                infoBox->show();
            }else{
                QMessageBox* infoBox = new QMessageBox(QMessageBox::Critical, tr("信息提示"), tr("表格数据删除失败！"));
                QTimer::singleShot(2000, infoBox,&QMessageBox::deleteLater);
                infoBox->show();
            }

        }
    }
    p0_searchTableData();
}

void MainInterface::p0_tableFieldComboxShow(QString tableName)
{
    ui->tableFieldSelectComboBox->clear();

    QSqlQuery query(QSqlDatabase::database("mysql_connection1"));
    //添加表字段信息
    QString tmpSql = QString("DESCRIBE ") + tableName;
    //qDebug()<<tmpSql;
    if(!query.exec(tmpSql)){
        qDebug()<<query.lastError().databaseText();
    }else{
        int primarkKeyColumnIndex = 0;//主键的列号
        while(query.next()){
            QString fieldName = query.value(0).toString();
            //qDebug()<<fieldName;
            ui->tableFieldSelectComboBox->addItem(p0_m_tableFieldName2UseHash[QPair(p0_m_use2TableNameHash[ui->tableSelectComboBox->currentText()] , fieldName)]);
            //获得主键名和列
            if(query.value(3).toString() == "PRI"){
                p0_m_tablePrimaryKeyField = fieldName;
                p0_m_primaryKeyColumnIndex = primarkKeyColumnIndex;
                //qDebug()<<"p0_m_tablePrimaryKey:"<<p0_m_tablePrimaryKey;
            }
            primarkKeyColumnIndex++;
        }
    }

    //添加权限信息表内容
    if(tableName == "permission_manage_information" || tableName == "teacher_information"){
        p0_m_permissionTableFieldSelect2ComboxHash.clear();
        p0_m_combox2PermissionTableFieldSelectHash.clear();
        p0_getPermissionInformation();
    }
}

bool MainInterface::p0_judgePrimaryKeyIsExistence(QVariant primaryKeyField)
{
    QSqlQuery query(QSqlDatabase::database("mysql_connection1"));
    QString tmpSql = "SELECT ";
    tmpSql += p0_m_tablePrimaryKeyField.toString();
    tmpSql += QString(" FROM ") + p0_m_use2TableNameHash[ui->tableSelectComboBox->currentText()];
    tmpSql += QString(" WHERE ") + p0_m_tablePrimaryKeyField.toString() + QString(" = ");
    if(primaryKeyField.type() == QVariant::String){
        tmpSql += QString("\'%1\'").arg(primaryKeyField.toString());
    }else{
        tmpSql += QString("%1").arg(primaryKeyField.toString());
    }

    qDebug()<<"judgePrimaryKeyIsExistence sql:"<<tmpSql;
    if(!query.exec(tmpSql)){
        qDebug()<<query.lastError().databaseText();
    }else{
        while(query.next()){
            return true;
        }
         return false;
    }
    return true;
}

bool MainInterface::p0_importData2DataBase(QStringList filePaths)
{
    QList<bool> execResultList;
    bool execResult = false;
    QSqlDatabase db = QSqlDatabase::database("mysql_connection1");    
    if(db.transaction()){
        QSqlQuery query(db);
        query.exec("START TRANSACTION");
        foreach(QString filePath, filePaths) {
            QXlsx::Document xlsx(filePath);

            //INSERT INTO teacher_information (teacher_id,name,role_id) VALUES (?, ?, ?)
            QString tmpSql = QString("INSERT INTO ");
            tmpSql += p0_m_use2TableNameHash[ui->tableSelectComboBox->currentText()] + " (";

            QQueue<QString> tableFieldQueue;
            QHash<QString, QVariantList> tableAlterFiledValue;
            // 读取Excel文件中的数据

            for(int row = 1; row <= xlsx.dimension().rowCount(); row++) {
                // 获取每行的数据并插入到数据库中
                for(int col = 1; col <= xlsx.dimension().columnCount();col++){
                    if(row == 1){
                        tableFieldQueue.enqueue(p0_m_use2TableFieldNameHash[QPair(ui->tableSelectComboBox->currentText(), xlsx.read(row, col).toString())]);

                    }else{
                        //如果是权限信息管理表、教师信息表,则转换role_id字段信息
                        if((p0_m_use2TableNameHash[ui->tableSelectComboBox->currentText()] == QString("teacher_information")) && tableFieldQueue[col-1] == QString("role_id")){
                            qDebug()<<"p0_m_Combox2PermissionTableFieldSelectHash[xlsx.read(row, col).toString()]:"<<p0_m_combox2PermissionTableFieldSelectHash[xlsx.read(row, col).toString()];
                            QString  tmpData = p0_m_combox2PermissionTableFieldSelectHash[xlsx.read(row, col).toString()];
                            if(tmpData.size() == 0){//没有对应的权限职位数据
                                return false;
                            }else{
                                tableAlterFiledValue[tableFieldQueue[col-1]].append(tmpData);
                            }
                        }else{
                            tableAlterFiledValue[tableFieldQueue[col-1]].append(xlsx.read(row, col));
                        }
                    }
                }

                //拼查询语句
                if(row == 2){
                    int index = 0;
                    foreach (QString tableField, tableFieldQueue){
                        if(index > 0){
                            tmpSql += ",";
                        }
                        index++;
                        tmpSql += tableField;
                    }
                    tmpSql += ") VALUES (";
                    for(int i = 0;i < tableFieldQueue.size();i ++){
                        if(i > 0){
                            tmpSql += ",";
                        }
                        tmpSql += "?";
                    }
                    tmpSql += ")";
                }
            }

            qDebug()<<tmpSql;
            query.prepare(tmpSql);
            foreach (QString tableFiled, tableFieldQueue) {
                query.addBindValue(tableAlterFiledValue[tableFiled]);
            }

            execResult = query.execBatch();
            execResultList.append(execResult);
            if(!execResult) {//批量执行数据插入
                qDebug() <<  query.lastError().databaseText();

            }

            //如果是教师表，则向权限信息管理表中添加信息
            if(p0_m_use2TableNameHash[ui->tableSelectComboBox->currentText()] == QString("teacher_information")){

                tmpSql = QString("INSERT INTO permission_manage_information (user_id, role_id) VALUES (?,?)");
                query.prepare(tmpSql);

                query.addBindValue(tableAlterFiledValue["teacher_id"]);
                query.addBindValue(tableAlterFiledValue["role_id"]);

                execResult = query.execBatch();
                execResultList.append(execResult);
                if(!execResult) {//批量执行数据插入
                    qDebug() <<  query.lastError().databaseText();

                }

            }

            //如果是学生、教师表，则向注册表中添加信息
            if(p0_m_use2TableNameHash[ui->tableSelectComboBox->currentText()] == QString("teacher_information") || p0_m_use2TableNameHash[ui->tableSelectComboBox->currentText()] == QString("student_information")){
                //创建默认密码
                QVariantList passwordList;
                for(int row = 1; row <= xlsx.dimension().rowCount(); row++){
                    QString password = "12345678";
                    QCryptographicHash passwordHash(QCryptographicHash::Sha256);
                    QByteArray passwordSha256;
                    passwordSha256.append(password.toUtf8());
                    passwordHash.addData(passwordSha256);
                    password = passwordHash.result().toHex();
                    passwordList<<password;
                }

                tmpSql = QString("INSERT INTO login_information (account, password) VALUES (?,?)");
                query.prepare(tmpSql);
                query.addBindValue(tableAlterFiledValue[p0_m_tablePrimaryKeyField.toString()]);
                query.addBindValue(passwordList);

                execResult = query.execBatch();
                execResultList.append(execResult);
                if(!execResult) {//批量执行数据插入
                    qDebug() <<  query.lastError().databaseText();


                }
            }
        }

        foreach (bool result, execResultList) {
            if(result == false){
                if(!db.rollback())
                {
                    qDebug() << "数据库回滚失败"<<db.lastError().databaseText(); //回滚
                }else{
                    qDebug()<<"数据库回滚成功";
                }
                return false;
            }

        }

        if(db.commit()){
            return true;
        }else{
            return false;
        }
    }
    return false;
}


bool MainInterface::p0_exportData2XLSX()
{
    QString openFilePosition = "C:/Users/Administrator/Desktop";
    QString fileName = QFileDialog::getSaveFileName(this, "保存文件", openFilePosition, "表格/excel格式(*.xlsx)");
    qDebug()<<fileName;

    QXlsx::Document xlsx;
    QXlsx::Format format1;/*设置标题单元的样式*/
    format1.setFontSize(12);/*设置字体大小*/
    format1.setHorizontalAlignment(QXlsx::Format::AlignHCenter);/*横向居中*/
    //format1.setBorderStyle(QXlsx::Format::BorderThin);/*边框样式*/
    //format1.setFontBold(true);/*设置加粗*/

    if(!xlsx.selectSheet("表格数据")){/*在当前打开的xlsx文件中，找一个名字为ziv的sheet*/
        xlsx.addSheet("表格数据");//找不到的话就添加一个名为ziv的sheet
    }

    QString tableName = p0_m_use2TableNameHash[ui->tableSelectComboBox->currentText()];
    QSqlDatabase db = QSqlDatabase::database("mysql_connection1");
    QString tmpSql = QString("SELECT * FROM %1").arg(tableName);
    QSqlQuery query(db);
    if(query.exec(tmpSql)){
        //表头列
        QSqlRecord queryRecord(query.record());
        qDebug()<<"queryRecord.count():"<<queryRecord.count();
        for(int colNum = 0; colNum < queryRecord.count(); colNum++){
            //qDebug() <<  queryRecord.fieldName(colNum);
            xlsx.write(1, colNum+1, p0_m_tableFieldName2UseHash[QPair(p0_m_use2TableNameHash[ui->tableSelectComboBox->currentText()], queryRecord.fieldName(colNum))],format1);
        }

        //表格数据
        int rowNum = 2;
        while(query.next()){
            for(int colNum = 0; colNum < queryRecord.count(); colNum++){
                if(queryRecord.fieldName(colNum) == "role_id" && (tableName == "teacher_information" || tableName == "permission_manage_information")){
                    xlsx.write(rowNum, colNum + 1, p0_m_permissionTableFieldSelect2ComboxHash[query.value(colNum).toString()],format1);
                }else{
                    xlsx.write(rowNum, colNum + 1, query.value(colNum),format1);
                }
            }
            rowNum++;
        }
    }else{
        return false;
    }

    if(fileName.isEmpty())
        return false;

    xlsx.saveAs(fileName);//保存文件

    return true;
}

void MainInterface::p0_getPermissionInformation()
{
    QSqlQuery query(QSqlDatabase::database("mysql_connection1"));
    QString tmpSql = "SELECT role_id, role_name FROM permission_information";
    if(!query.exec(tmpSql)){
        qDebug()<<query.lastError().databaseText();
    }else{
        while(query.next()){
            p0_m_permissionTableFieldSelect2ComboxHash[query.value(0).toString()]=query.value(1).toString();
            p0_m_combox2PermissionTableFieldSelectHash[query.value(1).toString()]=query.value(0).toString();
        }
    }
}

void MainInterface::p0_searchTableData()
{

    p0_m_nowPage = 1;
    if(ui->searchLineEdit->text().size() == 0){
        p0_allTableInformationShow();
    }else{

        p0_partTableInformationShow();
    }
}

